"""
Case Type   : nan
Case Name   : 带索引_is [not] infinite作为where条件性能测试
Create At   : 2024/10/24
Owner       : li-xin12345
Description :
    1、准备数据
    2、关闭enable_seqscan
    3、执行单次查询
    4、循环执行10w次查询，记录耗时
    5、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、成功
History     :
"""

import os
import re
import unittest

from yat.test import Node

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class IsInfiniteTest(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbAmode')
        self.pri_user = Node('PrimaryDbAmode')
        self.constant = Constant()
        self.common = Common()
        self.tb_name = 't_is_infinite_0023'
        self.index_name = 'i_is_infinite_0023'

    def test_is_infinite(self):
        text = '-----step1:准备数据;expect:成功-----'
        self.log.info(text)
        sql = f'''drop table if exists {self.tb_name} cascade;
        create table {self.tb_name} (v1 int, v2 varchar(255), 
        v3 float4, v4 float8);
        create index {self.index_name} on {self.tb_name} (v1);
        insert into {self.tb_name} values(1, 'test', 666, 999);
        insert into {self.tb_name} values(2, 'end', -3.402E+38, -3.402E+38);
        insert into {self.tb_name} values(3, 'end', 3.402E+38, 3.402E+38);
        '''
        sql_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, sql_res,
                      f'执行失败：{text}')
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_res,
                      f'执行失败：{text}')
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, sql_res,
                      f'执行失败：{text}')
        self.assertIn('INSERT 0 1', sql_res, f'执行失败：{text}')
        self.assertEqual(sql_res.count('INSERT 0 1'), 3, f'执行失败：{text}')

        text = '-----step2:关闭enable_seqsca;expect:成功-----'
        self.log.info(text)
        res = self.pri_sh.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG, 'enable_seqscan=off')
        self.log.info(res)
        self.assertTrue(res)
        res = self.pri_sh.restart_db_cluster()
        self.log.info(res)

        text = '-----step3:执行单次查询;expect:成功-----'
        self.log.info(text)
        sql = f'select v1, v2, v3, v4 from {self.tb_name} ' \
              f'where v3 is not infinite or v4 is infinite;'
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn('3 rows', res, f'执行失败：{text}')

        text = '-----step4:循环执行10w次查询，记录耗时;expect:成功-----'
        self.log.info(text)
        sql = f'''
        do \$\$
        declare
          i int;
          start_time timestamp;
          end_time timestamp;
        begin
          start_time := clock_timestamp();
          for i in 1..100000 loop
            execute 'select v1, v2, v3, v4 from {self.tb_name} 
            where v3 + ' || i || ' is not infinite or 
            v4 + ' || i || ' is infinite';
            if i % 10000 = 0 then
              raise notice '执行第 % 次查询', i;
            end if;
          end loop;
          end_time := clock_timestamp();
          raise notice '查询执行时间: % 秒', 
          extract(epoch from (end_time - start_time));
        end \$\$;
        '''
        self.log.info(sql)
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.CREATE_ANONYMOUS_BLOCK_SUCCESS_MSG, res,
                      f'执行失败：{text}')
        find_time = re.search(r"查询执行时间: ([\d.]+) 秒", res)
        time = [round(float(find_time.group(1)), 1) if find_time else None]
        self.log.info(time)
        self.assertLess(time[0], 16.30, f'执行失败：{text}')
        self.log.info('此处对比数据为需求测试时oracle侧测试结果，若高于此结果，性能达标')

    def tearDown(self):
        text = '-----step5:清理环境; expect:成功-----'
        self.log.info(text)
        sql = f'drop table if exists {self.tb_name} cascade;'
        drop_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(drop_res)
        restore_res = self.pri_sh.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG, 'enable_seqscan=on')
        self.log.info(restore_res)
        res = self.pri_sh.restart_db_cluster()
        self.log.info(res)

        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_res,
                      f'执行失败：{text}')
        self.assertTrue(restore_res)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')